declare @table varchar(50), @namespace varchar(50), @classname varchar(50)
set @table = 'Users'
set @classname = 'User'
set @namespace = 'SaberCity.Model.Core'

--select column_name, * from information_schema.columns where table_name = @table

-- GENERATE THE FIELDS
	select 'using System;' 
	union all
	select 'using BigfootMVC.MvcBase;' 
	union all
	select ''
	union all
	select 'namespace ' + @namespace 
	union all
	select '{' 
	union all
	select '	public class ' + @classname + ' : BaseModel' 
	union all
	select '{' 
	union all
	
	select 
		'public ' +
		case data_type 
			when 'int' then 'int'
			when 'varchar' then 'string'
			when 'nvarchar' then 'string'
			when 'smalldatetime' then 'DateTime'
			when 'text' then 'string'
			when 'datetime' then 'DateTime'
			when 'decimal' then 'decimal'
			when 'money' then 'decimal'
			when 'bit' then 'bool'
		end
		+ ' ' + column_name + ' { get; set; } ' 
	from information_schema.columns
	where table_name = @table

--VALIDATION
union all
select '' 
union all
select '	public override void ExecuteValidation()
			{'
union all        
	select 
		'	Validate.That(' + column_name + ', "' + column_name + '").' +
		case data_type 
			when 'varchar' then 'IsNotEmpty()'
			when 'nvarchar' then 'IsNotEmpty()'
			when 'smalldatetime' then 'IsNotMinMaxValue()'
			when 'text' then 'IsNotEmpty()'
			when 'datetime' then 'IsNotMinMaxValue()'
			when 'decimal' then 'Not().IsZero()'
			when 'int' then 'Not().IsZero()'
			when 'money' then 'Not().IsZero()'		
		end
		+ ';'
	from information_schema.columns
	where table_name = @table and is_nullable = 'NO' and data_type not in ('bit') and not (column_name = 'id' and data_type = 'int')
union all 
select '	}' 
union all
select '}'

--GENERATE CLASS WITH COLUMN NAMES
union all
select '#region FieldNames' 
union all
select 'public class ' + @classname + 'FieldNames' 
union all
select '{' 
union all
select 'public const string ' + column_name + ' = "' + column_name + '";' from information_schema.columns where table_name = @table
union all
select '}'
union all
select '#endregion'
union all
select '}'


--INSERT METHOD & UPDATE METHODS
DECLARE @Columns varchar(8000)
DECLARE @Values varchar(8000)
DECLARE @ColumnName varchar(8000)
DECLARE @sql varchar(8000)
SELECT @Columns = '', @Values = '', @ColumnName = '', @sql = ''

--	INSERT
DECLARE C1 CURSOR READ_ONLY 
FOR select column_name from information_schema.columns where table_name = @table and not (column_name = 'id' and data_type = 'int')	
OPEN C1
FETCH NEXT FROM C1 INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN	   
   IF LEN(@Columns) > 0 SET @Columns = @Columns + ', '
   SET @Columns = @Columns + @ColumnName
   
   IF LEN(@Values) > 0 SET @Values = @Values + ', '
   SET @Values = @Values + 'data.' + @ColumnName	   	   
   
   FETCH NEXT FROM C1 INTO @ColumnName	   	   
END
CLOSE C1		
DEALLOCATE C1
SET @sql = @sql + 'public static int Add' + @classname + '(' + @classname + ' data) {'
SET @sql = @sql + 'return DB.INSERTINTO("' + @table + '", "' + @Columns + '").VALUES(' + @values + ').ExecuteScalarIdentity();'
SET @sql = @sql + '}'
select @sql AS InsertMethod
	

--	UPDATE
DECLARE @Sets varchar(8000)	
SELECT @Sets = '', @sql = '', @ColumnName = ''

DECLARE C2 CURSOR READ_ONLY 
FOR select column_name from information_schema.columns where table_name = @table and not (column_name = 'id' and data_type = 'int')

OPEN C2
FETCH NEXT FROM C2 INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN	   
   SET @Sets = @Sets + '.SET("' + @ColumnName + '", data.' + @ColumnName + ')'	   	   	   
   FETCH NEXT FROM C2 INTO @ColumnName	   	   
END
CLOSE C2	
DEALLOCATE C2
SET @sql = @sql + 'public static void Update' + @classname + '(' + @classname + ' data) {'
SET @sql = @sql + 'DB.UPDATE("' + @table + '")' + @sets + '.WHERE("id", data.Id).ExecuteNonquery();'
SET @sql = @sql + '}'
select @sql AS UpdateMethod

-- DELETE
DECLARE @deletesql varchar(8000)
set @deletesql = ''
SET @deletesql = @deletesql + 'public static void Delete' + @classname + '(int id)'
SET @deletesql = @deletesql + '{'
SET @deletesql = @deletesql + ' DB.DELETEFROM("' + @table + '").WHERE("Id", id).ExecuteNonquery();'
SET @deletesql = @deletesql + '}'
select @deletesql AS DeleteMethod

-- GET
DECLARE @getsql varchar(8000)
set @getsql = ''
SET @getsql = @getsql + 'public static ' + @classname + ' Get' + @classname + '(int id)'
SET @getsql = @getsql + '{'
SET @getsql = @getsql + ' return DB.SELECT_ALL_FROM("' + @table + '").WHERE("Id", id).ExecuteObject<' + @classname + '>();'
SET @getsql = @getsql + '}'
select @getsql AS GetMethod


























